﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Tongji_G_Sort20150106]
	(
		@CompanyId VARCHAR(500),
		@StartDate nchar(10),
		@EndDate nchar(10),
		@sort VARCHAR(3000) --,
		--@flag int
	)
AS
DECLARE @strSql VARCHAR(max),@strSql1 VARCHAR(max),@strSql2 VARCHAR(max),@strSql3 VARCHAR(max),@sWhere varchar(500)
--(Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh
declare @zd varchar(4000)	
	set @sWhere=''
	set @strSql2=''
	
	if @CompanyId<>'' and @CompanyId<>'0'	
	begin 
		set @sWhere=' Where Id in ('+@CompanyId+')'
	end 
--if 	@flag=1 --业绩统计
--begin
	set @zd=',ISNULL((Select Sum(IAA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IAA--新增毛利
	,ISNULL((Select Sum(IAB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) as IAB --续费毛利
	,ISNULL((Select Sum(qqqdfqzcml) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As qqqdfqzcml -- 非企支出
	
	--,ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From  [Order] Where CompanyId=c.Id and AuditDate>=@sRq and AuditDate <=@eRq  AND StateId>=1 AND (NOE_Flag<>2) ),0)+
	--		ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=c.Id and UpTime>=@sRq and UpTime <=@eRq and o.companyId=c.Id AND o.NOE_Flag<>2),0) As IAC--新增应收 AND (NOE_Flag<>2)|and oid IN (SELECT oid FROM [Order] o WHERE companyId=c.Id AND o.NOE_Flag<>2 )
	,ISNULL((Select Sum(IAC) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq 
	and CreateDate<=@eRq),0) as IAC --新增应收2014.12.30 查询历史记录，不再实时统计
	
	,ISNULL((Select Sum(CostPrice) From Project Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq and FinanceAudit=1 ),0)+
			ISNULL((SELECT sum(dch_yf) FROM Order_Change_Hedge WHERE CompanyId=c.Id and UpTime>=@sRq and UpTime <=@eRq ),0)+
			ISNULL((Select Sum(CostPrice) From Project Where CompanyId =c.Id and FinanceAudit=2 
			and  CreateDate>=@sRq and CreateDate<=@eRq and FinanceAuditDate>@eRq ),0) As IAD--新增应付

			 ,ISNULL((Select Sum(IAE) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq ),0) As IAE--成单金额
	,ISNULL((Select Sum(IAF) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IAF--尾款收入
	,ISNULL((Select Sum(IAG) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IAG--非营利性收入
		      
 --,0 As OAA--直接成本
	--,0 As OAB--间接成本
	--,0 As OBA--市场费用
	--,0 As OBB--日常生活
	--,0 As OBC--行政办公
	--,0 As OBD--财务费用
	--,0 As OBE--人力费用
	--,0 As OBF--部门基金
	--,0 As OBG--总办费用
	--,0 As OBH--其他费用
	--,0 As OCA--投资
	--,0 As OCB--借款
	--,0 As OCC--往来借款
	--,0 As ODA--有形资产
	--,0 As ODB--无形资产		
	
	-- ,0 As IBA--实有现金
	--,0 As IBB--实有银行
	--,0 As IBC--账户余额
	--,0 As IBD--应有现金
	--                                                                                                                                      --
	--,0 As IBDA--现金收入
	--,0 As IBDB--现金支出
	--,0 As IBE--应有银行
	--,0 As IBEA--银行收入
	--,0 As IBEB--银行支出
	--,0 As IBF--借款合计
	--,0 As IBG--资金合计
	--,0 As IBH--应有现金平整
	--,0 As IBHA--现金平整收入
	--,0 As IBHB--现金平整支出
	--,0 As IBI--应有银行平整
	--,0 As IBIA--银行平整收入
	--,0 As IBIB--银行平整支出
	--,0 As IBJ--差额
	--,0 As IBK--应收合计
	--,0 As IBL--应付合计
		
			'
	
	SET @strSql='

	Declare @sRq Datetime,@eRq Datetime,@eRq_s Datetime
	Set @sRq = Convert(Datetime, '''+@StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@EndDate + ' 23:59:59'')
	Set @eRq_s = Convert(Datetime, '''+@EndDate + ' 00:00:00'')
	
	
	;WITH list As(Select *,ROW_NUMBER() OVER (ORDER BY '+@sort+')AS Row from (select 
	c.Id As CompanyId
	,c.CompanyName,dwbh,Mid
	
	 '
	 set @strSql1=@zd
	 
	 SET @strSql2='
	                                                                                                                                              
	
	From Company c  '+@sWhere+') as nb
	)
	
	
	Select * From list,(SELECT SUM(IAA) AS IAA_a,SUM(IAB) AS IAB_a,SUM(qqqdfqzcml) AS qqqdfqzcml_a,SUM(IAC) AS IAC_a,SUM(IAD) AS IAD_a,SUM(IAE) AS IAE_a,
	SUM(IAF) AS IAF_a,SUM(IAG) AS IAG_a 
	
	--,SUM(OAA) AS OAA_a,SUM(OAB) AS OAB_a,SUM(OBA) AS OBA_a,SUM(OBB) AS OBB_a
	--,SUM(OBC) AS OBC_a,SUM(OBD) AS OBD_a,SUM(OBE) AS OBE_a,SUM(OBF) AS OBF_a
	--,SUM(OBG) AS OBG_a,SUM(OBH) AS OBH_a,SUM(OCA) AS OCA_a,SUM(OCB) AS OCB_a,SUM(OCC) AS OCC_a
	--,SUM(ODA) AS ODA_a,SUM(ODB) AS ODB_a,
	
	--SUM(IBA) AS IBA_a,SUM(IBB) AS IBB_a,SUM(IBD) AS IBD_a,SUM(IBE) AS IBE_a
	--,SUM(IBC) AS IBC_a,SUM(IBF) AS IBF_a,SUM(IBG) AS IBG_a,SUM(IBH) AS IBH_a
	--,SUM(IBI) AS IBI_a,SUM(IBJ) AS IBJ_a,SUM(IBK) AS IBK_a,SUM(IBL) AS IBL_a
	 FROM list) AS n 
	 '
	 SET @strSql3=' ORDER BY '+@sort+''
	 PRINT (@strSql+@strSql1+@strSql2)
EXEC(@strSql+@strSql1+@strSql2+@strSql3)

	RETURN
